/* ============================================================ */
/*   Database name:  MODEL_2                                    */
/*   DBMS name:      Microsoft SQL Server 7.x                   */
/*   Created on:     04/07/05  10:18 a.m.L                    */
/* ============================================================ */

--desde Dom 30 Oct 2005


CREATE DATABASE SALEP01
GO
USE SALEP01
GO


/* ============================================================ */
/*   Table: DOCENTE                                             */
/* ============================================================ */
create table DOCENTE
(
    DOCE_id_z                 int               IDENTITY(1,1),
    DOCE_nombres_v            varchar(30)           not null,
    DOCE_apellidos_v          varchar(30)           not null,
    DOCE_telefono_v           varchar(25)           not null    ,
    DOCE_sexo_b               bit                   null    ,
    DOCE_contras_v               varchar(10)           null    ,
    constraint PK_DOCENTE primary key (DOCE_id_z)
)
go



/* ============================================================ */
/*   Table: GRADOACT
      Registra si el sistema tiene o no una seccion activa para que alumnos comiencen a trabajar.
      El campo (GRAD_estado) permite decir si:
	1: No hay seccion activada por docente
	2: Hay secc definida por docente act
	3: Docente termina sesion de trabajo con su seccion de clases
*/

/* ============================================================ */
create table GRADOACT
(
    GRAD_id_c                 char(5)            not null,
    DOCE_id_z                 int               not null,
    GRAD_estado               tinyint             not null,
    constraint PK_GRADOACT primary key (GRAD_id_c)
)
go




/* ============================================================ */
/*   Table: ESCUELA                                             */
/* ============================================================ */
create table ESCUELA
(
    ESCU_id_z                 tinyint               not null,
    ESCU_nombre_v             varchar(50)           not null,
    ESCU_direccion_v          varchar(100)          null    ,
    ESCU_telefono_v           varchar(30)           null    ,
    ESCU_director_v           varchar(30)           null    ,
    constraint PK_ESCUELA primary key (ESCU_id_z)
)
go

/* ============================================================ */
/*   Table: MODULO                                              */
/* ============================================================ */
create table MODULO
(
    MODU_id_z                 tinyint               not null,
    MODU_nombre_v             varchar(25)           not null,
    MODU_descripcion_v        varchar(500)          null    ,
    constraint PK_MODULO primary key (MODU_id_z)
)
go

/* ============================================================ */
/*   Table: GRADO                                               */
/* ============================================================ */
create table GRADO
(
    GRAD_id_c                 char(5)            not null,
    GRAD_grad_z                 tinyint            not null,
    GRAD_sec_z                 tinyint            not null, 
    GRAD_turno_z              tinyint               not null,
    GRAD_nombre_v             varchar(20)           not null,
    ESCU_id_z                 tinyint               not null,
    DOCE_id_z                 int               not null,
    constraint PK_GRADO primary key (GRAD_id_c)
)
go

/* ============================================================ */
/*   Index: ESCU_GRAD_FK                                        */
/* ============================================================ */
create index ESCU_GRAD_FK on GRADO (ESCU_id_z)
go

/* ============================================================ */
/*   Index: DOCE_GRAD_FK                                        */
/* ============================================================ */
create index DOCE_GRAD_FK on GRADO (DOCE_id_z)
go

/* ============================================================ */
/*   Table: ALUMNO                                              */
/* ============================================================ */
create table ALUMNO
(
    ALUM_codigo_c             char(5)               not null,
    GRAD_id_c                 char(5)            not null,
    ALUM_nombres_v            varchar(30)           not null,
    ALUM_apellidos_v          varchar(30)           not null,
    ALUM_edad_n               numeric(2)            not null,
    ALUM_sexo_b               bit                   not null,
    ALUM_login_v              varchar(10)           not null,
    ALUM_contrasenia_v        varchar(10)           not null,

    ALUM_numlista_d              int           null,
    ALUM_numu_d        int           IDENTITY(1,1),

    constraint PK_ALUMNO primary key (ALUM_codigo_c)
)
go

/* ============================================================ */
/*   Index: GRAD_ALUM_FK                                        */
/* ============================================================ */
create index GRAD_ALUM_FK on ALUMNO (GRAD_id_c)
go

/* ============================================================ */
/*   Table: NIVEL                                               */
/* ============================================================ */
create table NIVEL
(
    MODU_id_z                 tinyint               not null,
    NIVE_id_z                 tinyint               not null,
    NIVE_nombre_v             varchar(60)           not null,
    NIVE_indicacion_v         varchar(100)           not null,
    NIVE_tiempos_v        varchar(70)          not null,
    NIVE_descripcion_v        varchar(500)          null    ,
    NIVE_rutaresps_v         varchar(200)               null,
    NIVE_rutademo_v         varchar(200)               null,
    constraint PK_NIVEL primary key (MODU_id_z, NIVE_id_z)
)
go

/* ============================================================ */
/*   Index: MODU_NIVE_FK                                        */
/* ============================================================ */
create index MODU_NIVE_FK on NIVEL (MODU_id_z)
go

/* ============================================================ */
/*   Table: EJERCICIO                                           */
/* ============================================================ */
create table EJERCICIO
(
    MODU_id_z                 tinyint               not null,
    NIVE_id_z                 tinyint               not null,
    EJER_id_n                 numeric(3)            not null,
    EJER_texto_v              varchar(1000)         not null,
    EJER_repeticion_z         tinyint               not null,
    EJER_puntaje_z        tinyint           not null,
    EJER_tiempo_z        tinyint           not null,
    constraint PK_EJERCICIO primary key (MODU_id_z, NIVE_id_z, EJER_id_n)
)
go

/* ============================================================ */
/*   Index: PRAC_EJER_FK                                        */
/* ============================================================ */
create index PRAC_EJER_FK on EJERCICIO (MODU_id_z, NIVE_id_z)
go

/* ============================================================ */
/*   Table: PREGUNTA                                            */
/* ============================================================ */
create table PREGUNTA
(
    PREG_id_c                 char(8)              not null,
    MODU_id_z                 tinyint               null    ,
    NIVE_id_z                 tinyint               null    ,
    EJER_id_n                 numeric(3)            null    ,
    PREG_texto_v              varchar(150)           not null,
    PREG_puntaje_z            tinyint               not null,
    PREG_numrespcorrecta_v    varchar(15)           not null,
    PREG_totpalab 		int		not null,
    constraint PK_PREGUNTA primary key (PREG_id_c)
)
go

/* ============================================================ */
/*   Index: RELATIONSHIP_11_FK                                  */
/* ============================================================ */
create index RELATIONSHIP_11_FK on PREGUNTA (MODU_id_z, NIVE_id_z, EJER_id_n)
go

/* ============================================================ */
/*   Table: BITACORA                                            */
/* ============================================================ */
create table BITACORA
(
    BITA_id_c                 varchar(10)                   not null,
    ALUM_codigo_c             char(5)               not null,
    MODU_id_z                 tinyint               not null,
    BITA_repetmodu_z         tinyint               not null,
    BITA_estadomodu_z       tinyint               not null,
    BITA_puntosacum_z	int	null default(0),
--    BITA_notamodu_z	decimal	null default(0),
    BITA_fecha_d     datetime       null,
    constraint PK_BITACORA primary key (BITA_id_c)
)
go

/* ============================================================ */
/*   Index: ALUM_BITA_FK                                        */
/* ============================================================ */
create index ALUM_BITA_FK on BITACORA (ALUM_codigo_c)
go

/* ============================================================ */
/*   Index: MODU_BITA_FK                                        */
/* ============================================================ */
create index MODU_BITA_FK on BITACORA (MODU_id_z)
go

/* ============================================================ */
/*   Table: DEBITACORA                                            */
/* ============================================================ */
create table DEBITACORA
(
    BITA_id_c                 varchar(10)                   not null,
    NIVE_id_z                 tinyint               not null,
    DEBI_repeticnivel_z         tinyint               not null,
    DEBI_intento_z           tinyint               not null,
    DEBI_estado_z              tinyint	not null,
    DEBI_fechaIni_d              datetime              not null,
    DEBI_fechaFin_d              datetime              null,
    DEBI_ejerpendientes_c              varchar(30)	null,
    DEBI_ejerbuenos_c              varchar(30)	null,
    DEBI_puntaje_z            int               not null,
    DEBI_velocProm_n	     decimal(8,4) null default(0.00)
    constraint PK_DEBITACORA primary key (BITA_id_c,NIVE_id_z,DEBI_repeticnivel_z,DEBI_intento_z)
)
go


/* ============================================================ */
/*   Index: BITA_DEBI_FK                                        */
/* ============================================================ */
create index BITA_DEBI_FK on DEBITACORA (BITA_id_c)
go



create table ESTADO(
    ESTA_estado_z              tinyint	not null,
    ESTA_descripestado_v              varchar(15)              not null,
    constraint PK_ESTADO primary key (ESTA_estado_z)
)
GO







/* ============================================================ */
/*   Table: RESPUESTA                                           */
/* ============================================================ */
create table RESPUESTA
(
    RESP_id_z                 tinyint               not null,
    PREG_id_c                 char(8)              not null,
    RESP_texto_v              varchar(80)           not null,
    constraint PK_RESPUESTA primary key (RESP_id_z, PREG_id_c)
)
go

/* ============================================================ */
/*   Index: PREG_RESP_FK                                        */
/* ============================================================ */
create index PREG_RESP_FK on RESPUESTA (PREG_id_c)
go

alter table GRADO
    add constraint FK_GRADO_ESCU_GRAD_ESCUELA foreign key  (ESCU_id_z)
       references ESCUELA (ESCU_id_z)
go

alter table GRADO
    add constraint FK_GRADO_DOCE_GRAD_DOCENTE foreign key  (DOCE_id_z)
       references DOCENTE (DOCE_id_z)
go

alter table ALUMNO
    add constraint FK_ALUMNO_GRAD_ALUM_GRADO foreign key  (GRAD_id_c)
       references GRADO (GRAD_id_c)
go

alter table NIVEL
    add constraint FK_NIVEL_MODU_NIVE_MODULO foreign key  (MODU_id_z)
       references MODULO (MODU_id_z)
go

alter table EJERCICIO
    add constraint FK_EJERCICIO_NIVE_EJER_NIVEL foreign key  (MODU_id_z, NIVE_id_z)
       references NIVEL (MODU_id_z, NIVE_id_z)
go

alter table PREGUNTA
    add constraint FK_PREGUNTA_RELATIONS_EJERCICI foreign key  (MODU_id_z, NIVE_id_z, EJER_id_n)
       references EJERCICIO (MODU_id_z, NIVE_id_z, EJER_id_n)
go

alter table RESPUESTA
    add constraint FK_RESPUEST_PREG_RESP_PREGUNTA foreign key  (PREG_id_c)
       references PREGUNTA (PREG_id_c)
go

alter table BITACORA
    add constraint FK_BITACORA_ALUM_BITA_ALUMNO foreign key  (ALUM_codigo_c)
       references ALUMNO (ALUM_codigo_c)
go

alter table BITACORA
   add constraint FK_BITACORA_MODU_BITA_MODULO foreign key (MODU_id_z)
      references MODULO(MODU_id_z)
go

alter table DEBITACORA
   add constraint FK_DEBITACORA_BITA_DEBI_BITACORA foreign key (BITA_id_c)
      references BITACORA(BITA_id_c)
go





/* Crea una serie de desencadenadores para algunas tablas de la BDD*/

CREATE trigger CrearCodigoPregunta
on PREGUNTA
for insert
as
--Forma el codigo unico para la llave primaria de la tabla (PREGUNTA)
  declare @modu_id tinyint
  declare @nive_id tinyint
  declare @ejer_id numeric(3)
  declare @preg_id char(8)
  declare @preg_idFIN char(8)
  
  select @modu_id = MODU_id_z from inserted
  select @nive_id = NIVE_id_z from inserted
  select @ejer_id = EJER_id_n from inserted
  select @preg_id = PREG_id_c from inserted

  select @preg_idFIN =right("00"+LTRIM(STR(@modu_id)),2)+right("000"+LTRIM(STR(@nive_id)),2)+right("000"+LTRIM(STR(@ejer_id)),2)+'-'+right(ltrim(rtrim(@preg_id)),1)

  begin tran
--  if (@existencia<@unidades) goto errores
  update PREGUNTA set PREG_id_c=@preg_idFIN where PREG_id_c = @preg_id
  commit tran
--  goto fin
--  errores:
--  rollback tran
--  fin:

GO

CREATE trigger CrearIdGrado
on GRADO
for insert
as
--Forma el codigo unico para la llave primaria de la tabla (GRADO)
  declare @grado_id tinyint
  declare @sec_id tinyint
  declare @turno_id tinyint
  declare @grado_idFIN char(5)
  
  select @grado_id = GRAD_grad_z from inserted
  select @sec_id = GRAD_sec_z from inserted
  select @turno_id = GRAD_turno_z from inserted

  select @grado_idFIN =right(LTRIM(STR(@grado_id)),1)+'-'+right("000"+LTRIM(STR(@sec_id)),1)+'-'+right("000"+LTRIM(STR(@turno_id)),1)

  begin tran
--  if (@existencia<@unidades) goto errores
  update GRADO set GRAD_id_c=@grado_idFIN where GRAD_id_c = '0'
  commit tran
--  goto fin
--  errores:
--  rollback tran
--  fin:

GO



CREATE trigger CrearCodigoAlumno
on ALUMNO
for insert
as
--Forma el codigo unico para la llave primaria de la tabla (GRADO)
  declare @alum_numu int
  declare @alum_codigoFIN char(5)
  
  select @alum_numu = ALUM_numu_d from inserted
  select @alum_codigoFIN ='AL'+right("000"+LTRIM(STR(@alum_numu)),3)

  begin tran
--  if (@existencia<@unidades) goto errores
  update ALUMNO set ALUM_codigo_c=@alum_codigoFIN where ALUM_numu_d = @alum_numu
  commit tran
--  goto fin
--  errores:
--  rollback tran
--  fin:

GO



CREATE trigger CrearCodigoBitacora
on BITACORA
for insert
as
--Forma el codigo unico para llave primaria de la tabla (BITACORA)
  declare @alum_id varchar(5)
  declare @modu_id tinyint
  declare @repetmodu_id numeric(3)
  declare @bita_id varchar(10)
  declare @bita_idFIN varchar(10)

  
  select @alum_id = ALUM_codigo_c from inserted
  select @modu_id = MODU_id_z from inserted
  select @repetmodu_id = BITA_repetmodu_z from inserted
  select @bita_id = BITA_id_c from inserted

  select @bita_idFIN =@alum_id+'-'+LTRIM(STR(@modu_id))+'-'+LTRIM(STR(@repetmodu_id))

  begin tran
--  if (@existencia<@unidades) goto errores
  update BITACORA set BITA_id_c=@bita_idFIN, BITA_fecha_d=GETDATE() where BITA_id_c = @bita_id
  commit tran
--  goto fin
--  errores:
--  rollback tran
--  fin:
GO



CREATE trigger ModificarFechaBitacora
on BITACORA
for update
as
  declare @bita_id varchar(10)
--actualiliza la fecha del reg de Bitacora actual
  select @bita_id = BITA_id_c from inserted

  begin tran
--  if (@existencia<@unidades) goto errores
  update BITACORA set BITA_fecha_d=GETDATE() where BITA_id_c = @bita_id
  commit tran
--  goto fin
--  errores:
--  rollback tran
--  fin:

GO


